﻿Imports DTO
Imports System.Data.SqlClient

Public Class ServiceDAO
    Public Function GetListService() As List(Of ServiceDTO)
        Dim listResult As New List(Of ServiceDTO)
        Try
            Dim sql As String = "select ServiceID, isNull(Price,0) Price, isNull(Name,'') Name from Service  order by Price "
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read)
                Dim service As New ServiceDTO
                service.ServiceID = reader("ServiceID")
                service.Price = reader("Price")
                service.Name = reader("Name")
                listResult.Add(Service)
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Throw ex
        End Try
        Return listResult
    End Function

    Public Function GetListServiceByBooking(ByVal bookingId As Integer) As List(Of ServiceDTO)
        Dim listResult As New List(Of ServiceDTO)
        Try
            Dim sql As String = "select s.ServiceID, isNull(s.Price,0) Price, isNull(s.Name,'') Name, isNull(ord.Quantity,0) Quantity from Service s, Receipt re, Orders ord  "
            sql = String.Format("{0} where re.ReceiptId=ord.ReceiptId and s.ServiceId=ord.ServiceId and re.BookingId={1}", sql, bookingId)
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read)
                Dim service As New ServiceDTO
                service.ServiceID = reader("ServiceID")
                service.Price = reader("Price")
                service.Name = reader("Name")
                service.Quantity = reader("Quantity")
                listResult.Add(service)
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Throw ex
        End Try
        Return listResult
    End Function

 
End Class
